Progress-to-ORACLE Incremental Schema Migration utility
The Incremental Schema Migration utility allows you to migrate schema changes from an OpenEdge database to an ORACLE database. For example, in the process of developing an application in the Progress 4GL that you will migrate to ORACLE, you might want to make and test schema changes in your OpenEdge database that you want reflected in the ORACLE database. The utility reads a delta.df file that has been created using the standard incremental dump procedure, and creates a delta SQL file, named <ORACLE-logical-db>.sql , that contains the SQL DDL for making the changes and a new delta .df file, named <schema-holder-name>.df. You can then load the .df file into the schema holder and apply the SQL file to the ORACLE instance to complete the migration process.
Note that you do not make schema changes directly in the schema holder, which must remain synchronized with the ORACLE database. The utility uses the schema holder to determine what the ORACLE definitions are.
|
To run the Incremental Schema Migration utility:
|
- From the Data Admin main menu, choose DataServers
ORACLE Utilities Schema Migration Tools Generate Delta.sql Progress to ORACLE. The following dialog box appears:
- Provide the information as described in Table 7–9:
Table 7–9: Delta df to ORACLE Conversion UI elements
|
Interface element
|
Description
|
|
Delta DF File
|
The name of the delta.df file that was created when you ran the incremental dump routine against two OpenEdge databases. You can browse for the filename by choosing the Files button.
|
|
Schema Holder Database
|
The name of the schema holder.
|
|
Connect parameters for Schema
|
By default, the current working database is specified. To connect to a different databases, specify the parameters to connect to the ORACLE schema holder to be updated.
|
|
Logical name for ORACLE database
|
Specify the ORACLE database logical name, that is, the name by which you refer to the ORACLE database in your application.
|
|
Oracle Object Owner Name
|
Enter the name of the owner.
|
|
ORACLE tablespace for Tables
|
Enter the names of any tablespaces to be used here.
|
|
ORACLE tablespace for Indexes
|
Enter the names of any tablespaces to be used for indexes here.
|
|
Create Progress RECID Field
|
Select this option if your ORACLE database currently contains the PROGRESS_RECID field. Selecting this option will maintain the use of PROGRESS_RECID in any new tables added by this utility.
|
|
Include Default
|
Check this toggle box to include initial values in column definitions.
|
|
Use Sql Width
|
Check this toggle box to use the _width field to calculate column size instead of using the format field.
|
|
Create schema holder delta.df
|
Check this toggle box if you want the utility to generate a .df file that includes the incremental schema information. You can then load this .df file into the schema holder. By default, this toggle box is checked.
|
- Choose OK. The utility generates a
delta.sql file and, optionally, a delta.df file.
- After running the utility, you must apply the SQL it generates to the ORACLE database and load the new
delta.df file into the original schema holder so that it is synchronized with the modified ORACLE database.
The utility generates SQL that will create objects in the ORACLE database. It creates the same objects as the Progress-to-ORACLE Migration utility. For example, Progress indexes are case-insensitive. To create this equivalent functionality in the ORACLE database, for an index defined in the OpenEdge database on a CHARACTER field, the utility generates SQL to use the UPPER function for the index. Table 7–10 describes the ORACLE equivalents of Progress object types.
Table 7–10: ORACLE equivalents of Progress objects
|
Progress object
|
ORACLE equivalents
|
|
Case-insensitive Index.
|
The index definition will use the UPPER function.
|
|
Array.
|
One column for each extent of the Progress array. The columns are named field-name##extent-number. For example, a Progress field called monthly-amount with an extent of 12 will have 12 columns in ORACLE with names such as MONTHLY_AMOUNT##1 through MONTHLY_AMOUNT##12.
|
|
Table.
|
If Create Progress RECID Field, was selected, for any new table, a PROGRESS_RECID column is added. This indexed column provides a unique key on the ORACLE table.
A sequence named table-name_SEQ is also added. This sequence populates the PROGRESS_RECID column for each row in the ORACLE table.
|
|
Deleted Field.
|
The column will be dropped from ORACLE.
|
Not all OpenEdge objects can be converted to ORACLE by this utility. Table 7–11 details restrictions on the update.
Table 7–11: Database modifications not converted to ORACLE
|
Database object
|
Modification in OpenEdge
|
Action
|
|
Sequence.
|
Starting value altered.
|
None. ORACLE does not allow the starting value of a sequence to be altered. You must manually drop and add the sequence to implement this change.
|
|
Trigger.
|
Any.
|
Applied to schema holder. Progress 4GL triggers are not converted to ORACLE SQL.
|
|
Character field.
|
Format altered.
|
None. ORACLE’s restrictions on the alteration of character fields, such as knowing if all fields are NULL prior to decreasing the width, cannot be accommodated by this utility, therefore no actions are implemented.
|
Table 7–12 shows how the fields of a Progress table convert to ORACLE equivalents.
Table 7–12: Sample object equivalents
|
Progress state table
|
ORACLE STATE table
|
|
Character field: State-Name
|
STATE_NAME
|
|
Array with 3 Extents: State-Fact
|
STATE_FACT##1
STATE_FACT##2
STATE_FACT##3
|
|
Default record identifier object
|
STATE##PROGRESS_RECID
STATE_SEQ
|
The utility ensures that the migrated objects have names that are unique to the ORACLE database. If you have given the object a name that is not unique, it drops characters from the end of the name and appends numbers until it creates a unique name. Since ORACLE requires that index names be unique to the database, the utility appends the table name to the indexed column name to create a unique name.